from sqlalchemy.orm import Session
from sqlalchemy import and_, or_
from typing import Optional, List
from decimal import Decimal
from math import ceil
import random
import string
import time

from ..models.product import Product
from ..schemas.product import ProductCreate, ProductUpdate, ProductQuery


class ProductService:
    """商品服务层"""

    @staticmethod
    def _generate_sku(db: Session, product_name: str) -> str:
        """
        生成商品SKU
        格式: PRD + 时间戳后6位 + 3位随机字符 + 名称前2位拼音首字母
        """
        # 生成时间戳后6位
        timestamp = str(int(time.time()))[-6:]

        # 生成3位随机字符（大写字母和数字）
        random_chars = ''.join(random.choices(string.ascii_uppercase + string.digits, k=3))

        # 提取商品名称前2个字符的拼音首字母（这里简化为直接取前2个字符的大写）
        name_prefix = product_name[:2].upper() if len(product_name) >= 2 else product_name[:1].upper()

        # 组合SKU
        sku = f"PRD{timestamp}{random_chars}{name_prefix}"

        # 检查SKU是否已存在，如果存在则重新生成
        existing_product = db.query(Product).filter(Product.sku == sku).first()
        if existing_product:
            return ProductService._generate_sku(db, product_name)

        return sku

    @staticmethod
    def create_product(db: Session, product_data: ProductCreate) -> Product:
        """创建商品"""
        # 自动生成SKU
        sku = ProductService._generate_sku(db, product_data.name)

        # 将产品数据转换为字典并添加生成的SKU
        product_dict = product_data.model_dump()
        product_dict['sku'] = sku

        db_product = Product(**product_dict)
        db.add(db_product)
        db.commit()
        db.refresh(db_product)
        return db_product

    @staticmethod
    def get_product_by_id(db: Session, product_id: int) -> Optional[Product]:
        """根据ID获取商品"""
        return db.query(Product).filter(Product.id == product_id).first()

    @staticmethod
    def get_product_by_sku(db: Session, sku: str) -> Optional[Product]:
        """根据SKU获取商品"""
        return db.query(Product).filter(Product.sku == sku).first()

    @staticmethod
    def get_products(
        db: Session,
        query: ProductQuery
    ) -> tuple[List[Product], int]:
        """获取商品列表（分页）"""
        # 构建查询条件
        filters = []

        if query.name:
            filters.append(Product.name.contains(query.name))

        if query.category_id:
            filters.append(Product.category_id == query.category_id)

        if query.min_price is not None:
            filters.append(Product.price >= query.min_price)

        if query.max_price is not None:
            filters.append(Product.price <= query.max_price)

        if query.is_active is not None:
            filters.append(Product.is_active == query.is_active)

        if query.is_featured is not None:
            filters.append(Product.is_featured == query.is_featured)

        # 应用过滤条件
        db_query = db.query(Product)
        if filters:
            db_query = db_query.filter(and_(*filters))

        # 获取总数
        total = db_query.count()

        # 分页
        offset = (query.page - 1) * query.size
        products = db_query.offset(offset).limit(query.size).all()

        return products, total

    @staticmethod
    def update_product(
        db: Session,
        product_id: int,
        product_data: ProductUpdate
    ) -> Optional[Product]:
        """更新商品"""
        db_product = db.query(Product).filter(Product.id == product_id).first()
        if not db_product:
            return None

        # 如果要更新SKU，检查是否已存在
        if product_data.sku and product_data.sku != db_product.sku:
            existing_product = db.query(Product).filter(
                and_(Product.sku == product_data.sku, Product.id != product_id)
            ).first()
            if existing_product:
                raise ValueError(f"SKU '{product_data.sku}' 已存在")

        # 更新字段
        update_data = product_data.model_dump(exclude_unset=True)
        for field, value in update_data.items():
            setattr(db_product, field, value)

        db.commit()
        db.refresh(db_product)
        return db_product

    @staticmethod
    def delete_product(db: Session, product_id: int) -> bool:
        """删除商品"""
        db_product = db.query(Product).filter(Product.id == product_id).first()
        if not db_product:
            return False

        db.delete(db_product)
        db.commit()
        return True

    @staticmethod
    def update_stock(db: Session, product_id: int, quantity: int) -> Optional[Product]:
        """更新商品库存"""
        try:
            # 查询商品
            db_product = db.query(Product).filter(Product.id == product_id).first()
            if not db_product:
                return None

            # 计算新库存
            current_stock = db_product.stock
            new_stock = current_stock + quantity

            # 检查库存是否足够
            if new_stock < 0:
                raise ValueError(f"库存不足：当前库存 {current_stock}，尝试减少 {abs(quantity)}")

            # 更新库存
            db_product.stock = new_stock

            # 提交事务
            db.commit()

            # 刷新对象以获取最新数据
            db.refresh(db_product)

            return db_product

        except Exception as e:
            # 回滚事务
            db.rollback()
            raise e

    @staticmethod
    def get_featured_products(db: Session, limit: int = 10) -> List[Product]:
        """获取推荐商品"""
        return db.query(Product).filter(
            and_(Product.is_featured == True, Product.is_active == True)
        ).limit(limit).all()

    @staticmethod
    def get_products_by_category(db: Session, category_id: int) -> List[Product]:
        """根据分类获取商品"""
        return db.query(Product).filter(
            and_(Product.category_id == category_id, Product.is_active == True)
        ).all()

    @staticmethod
    def search_products(db: Session, keyword: str) -> List[Product]:
        """搜索商品"""
        return db.query(Product).filter(
            and_(
                or_(
                    Product.name.contains(keyword),
                    Product.description.contains(keyword)
                ),
                Product.is_active == True
            )
        ).all()